using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration; 
namespace HermesPortal.Core.DAO
{
    class SagaDAO
    {
       internal  static Saga GetSaga(int id)
        {
             SqlDataReader reader = null;
             Saga saga = null;
          
            using (  SqlConnection conn = new SqlConnection(  ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString ))
            {
                SqlCommand cmd = new SqlCommand("SagaGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;   
                cmd.Parameters.AddWithValue("@SagaID",id); 
                cmd.Connection.Open();
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    saga = LoadSaga(reader);
                cmd.Connection.Close();  
            }
            return saga;
        }

        internal static int InsertSaga(Saga saga)
        {
            int sagaId = 0;
          
            using (  SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SagaInsert", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@TemplateStyle", saga.TemplateStyle );
                cmd.Parameters.AddWithValue("@Name",saga.Name);
                cmd.Parameters.AddWithValue("@TagLine",saga.TagLine);
                cmd.Parameters.AddWithValue("@Header",saga.Header);
                cmd.Parameters.AddWithValue("@CurrentAffiars",saga.CurrentAffairs);
                cmd.Parameters.AddWithValue("@SagaSummary", saga.SagaSummary);
                cmd.Parameters.AddWithValue("@FormationStory",saga.FormationStory);
                cmd.Parameters.AddWithValue("@FoundedMonth",saga.FoundedMonth );
                cmd.Parameters.AddWithValue("@FoundedYear", saga.FoundedYear );
                cmd.Parameters.AddWithValue("@CurrentYear", saga.CurrentYear );
                cmd.Connection.Open();
                object obj  = cmd.ExecuteScalar();
                if (obj != null)
                    sagaId = Convert.ToInt32(obj);
                cmd.Connection.Close();
              
            }
            return sagaId;
        }
        internal static List<Saga> GetSagas()
        {

            List<Saga> sagas = new List<Saga>();  
            SqlDataReader reader = null;
            
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SagasGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Saga saga = LoadSaga(reader);
                    sagas.Add(saga); 
                }
  
            }

            return sagas;
        }

        internal static int GetCreatorRoleForSaga( int creatorid, int sagaId)
        {
            int sagaRoleId = 0;
            
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("CreatorGetRoleForSaga", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CreatorId", creatorid);
                cmd.Parameters.AddWithValue("@SagaID", sagaId);
                cmd.Connection.Open();
                object obj = cmd.ExecuteScalar();
                if (obj != null)
                    sagaRoleId = Convert.ToInt32(obj);
                cmd.Connection.Close();
            }
            return sagaId;
        }
        internal static List<Saga> GetSagasByCreator(int creatorId)
        {
            List<Saga> sagas = new List<Saga>();
            SqlDataReader reader = null;
           
            using ( SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SagasGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CreatorID", creatorId);
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Saga saga = LoadSaga(reader);
                    sagas.Add(saga);
                }

            }

            return sagas;

        }
        internal static List<Saga> GetSagasByCreatorAndRole(int creatorId, int sagaRoleId)
        {
            List<Saga> sagas = new List<Saga>();
            SqlDataReader reader = null;
            
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SagasGetByCreatorRole", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CreatorID", creatorId);
                cmd.Parameters.AddWithValue("@SagaRoleID", creatorId);
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Saga saga = LoadSaga(reader);
                    sagas.Add(saga);
                }

            }
            return sagas;

        }
        internal static void UpdateSaga(Saga saga)
        {
    
            using ( SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SagaUpdate", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", saga.Id );
                cmd.Parameters.AddWithValue("@TemplateStyle", saga.TemplateStyle);
                cmd.Parameters.AddWithValue("@Name", saga.Name);
                cmd.Parameters.AddWithValue("@TagLine", saga.TagLine);
                cmd.Parameters.AddWithValue("@Header", saga.Header);
                cmd.Parameters.AddWithValue("@CurrentAffairs", saga.CurrentAffairs);
                cmd.Parameters.AddWithValue("@SagaSummary", saga.SagaSummary);
                cmd.Parameters.AddWithValue("@FormationStory", saga.FormationStory);
                cmd.Parameters.AddWithValue("@FoundedMonth", saga.FoundedMonth);
                cmd.Parameters.AddWithValue("@FoundedYear", saga.FoundedYear);
                cmd.Parameters.AddWithValue("@CurrentYear", saga.CurrentYear);
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }


        }
        public static List<SagaSummary> GetSagaSummarys()
        {
            List<SagaSummary> sagas = new List<SagaSummary>();
            SqlDataReader reader = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SagaSummarysGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;
              
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    SagaSummary sagaSummary = LoadSagaSummary(reader);
                    sagas.Add(sagaSummary);
                }

            }
            return sagas;

        }

        public static SortedList<int, string> GetSagaList()
        {
            SortedList<Int32, String> sagas = new SortedList<Int32, String>();
            SqlDataReader reader = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SagasGetIDAndName", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    sagas.Add((int)reader["SagaID"], reader["Name"].ToString());
                }

            }
            return sagas;

        }
        #region private helpers

        private static Saga LoadSaga(SqlDataReader reader)
        {
            Saga saga = new Saga(); 
            if (reader != null && !reader.IsClosed)
            {
                saga.Id = (int)reader["SagaID"];
                if (reader["TemplateStyle"] != DBNull.Value) saga.TemplateStyle = (int)reader["TemplateStyle"];
                if (reader["Name"] != DBNull.Value) saga.Name = (string)reader["Name"];
                if (reader["TagLine"] != DBNull.Value) saga.TagLine = (string)reader["TagLine"];
                if (reader["Header"] != DBNull.Value) saga.Header = (string)reader["Header"];
                if (reader["CurrentAffairs"] != DBNull.Value) saga.CurrentAffairs = (string)reader["CurrentAffairs"];
                if (reader["SagaSummary"] != DBNull.Value) saga.SagaSummary = (string)reader["SagaSummary"];
                if (reader["FormationStory"] != DBNull.Value) saga.FormationStory = (string)reader["FormationStory"];
                if (reader["FoundedDate"] != DBNull.Value) saga.FoundedDate = (string)reader["FoundedDate"];
                if (reader["FoundedMonth"] != DBNull.Value) saga.FoundedMonth  = (int)reader["FoundedMonth"];
                if (reader["FoundedYear"] != DBNull.Value) saga.FoundedYear   = (int)reader["FoundedYear"];
                if (reader["CurrentYear"] != DBNull.Value) saga.CurrentYear  = (int)reader["CurrentYear"];
           
            }
            return saga;
        }

        private static SagaSummary LoadSagaSummary(SqlDataReader reader)
        {
            SagaSummary sagaSummary = new SagaSummary();
            if (reader != null && !reader.IsClosed)
            {
                sagaSummary.Id = (int)reader["SagaID"];
                if (reader["Name"] != DBNull.Value) sagaSummary.Name = (string)reader["Name"];
                if (reader["TagLine"] != DBNull.Value) sagaSummary.TagLine = (string)reader["TagLine"];
                if (reader["Header"] != DBNull.Value) sagaSummary.Header = (string)reader["Header"];
                if (reader["FormationStory"] != DBNull.Value) sagaSummary.FormationStory = (string)reader["FormationStory"];
                if (reader["FoundedDate"] != DBNull.Value) sagaSummary.FoundedDate = (string)reader["FoundedDate"];
            }
            return sagaSummary;
        }
        #endregion 

    
      
    }

}

